/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import controllers.Vehicle;

/**
 *
 * @author Dan
 */
public class VehicleDAO extends DAOTest {

    public static List<Vehicle> getVehicles() {
        Connection con = getConnection();
        ArrayList<Vehicle> vehicles = new ArrayList<Vehicle>();
        try {
            CallableStatement cs = con.prepareCall("{call stp_selAllVehicles}");
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                int vehicleTypeID = rs.getInt("VehicleTypeID");
                String vehicleTypeName = rs.getString("VehicleTypeName");
                boolean isBrokenDown = rs.getBoolean("isBrokenDown");
                vehicles.add(new Vehicle(id, name, vehicleTypeID, vehicleTypeName, isBrokenDown));
            }



            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return vehicles;
    }

    public static Vehicle getVehicleByID(int vehicleID) {
        Connection con = getConnection();
        Vehicle returnVehicle = null;

        try {
            CallableStatement cs = con.prepareCall("{call stp_selVehicleByID(?)}");
            cs.setInt(1, vehicleID);
            ResultSet rs = cs.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("ID");
                String name = rs.getString("Name");
                int vehicleTypeID = rs.getInt("VehicleTypeID");
                String vehicleTypeName = rs.getString("VehicleTypeName");
                boolean isBrokenDown = rs.getBoolean("isBrokenDown");
                returnVehicle = new Vehicle(id, name, vehicleTypeID, vehicleTypeName, isBrokenDown);
            }

            cs.close();
            con.close();
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
        return returnVehicle;
    }

    public static boolean addVehicle(Vehicle vehicle) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_insVehicle(?, ?, ?)}");
            cs.setString(1, vehicle.getName());
            cs.setInt(2, vehicle.getVehicleTypeID());
            cs.setBoolean(3, vehicle.isBrokenDown());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    public synchronized static boolean deleteVehicle(Vehicle vehicle) {
        Connection con = getConnection();
        boolean result = false;
        try {
            CallableStatement cs = con.prepareCall("{call stp_delVehicle(?)}");
            cs.setInt(1, vehicle.getId());
            int delete = cs.executeUpdate();

            result = delete > 0;
        } catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        return result;
    }

    public static boolean saveVehicle(Vehicle vehicle) {
        Connection con = getConnection();
        boolean result = false;

        try {
            CallableStatement cs = con.prepareCall("{call dbo.stp_updVehicle(?, ?, ?, ?)}");
            cs.setInt(1, vehicle.getId());
            cs.setString(2, vehicle.getName());
            cs.setInt(3, vehicle.getVehicleTypeID());
            cs.setBoolean(4, vehicle.isBrokenDown());
            int added = cs.executeUpdate();

            result = added > 0;

            cs.close();
            con.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }
}
